package com.c6906.vdam.monitor.mapper;

import com.c6906.vdam.monitor.model.ServerData;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface ServerDataMapper {

    @Insert("INSERT INTO server_data(server_id,msg_serial, host_name, event_time, mem_total, mem_available, mem_used, mem_free, mem_used_percent, total_cpu_usage, created_at, updated_at) " +
            "VALUES(#{serverId},#{msgSerial}, #{hostName}, #{eventTime}, #{memTotal}, #{memAvailable}, #{memUsed}, #{memFree}, #{memUsedPercent}, #{totalCPUUsage}, #{createdAt}, #{updatedAt})")
    void insertServerData(ServerData serverData);

    @Select("SELECT * FROM server_data WHERE server_id = #{serverId}")
    List<ServerData> findByServerId(Long serverId);

    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "serverId", column = "server_id"),
            @Result(property = "msgSerial", column = "msg_serial"),
            @Result(property = "hostName", column = "host_name"),
            @Result(property = "eventTime", column = "event_time"),
            @Result(property = "memTotal", column = "mem_total"),
            @Result(property = "memAvailable", column = "mem_available"),
            @Result(property = "memUsed", column = "mem_used"),
            @Result(property = "memFree", column = "mem_free"),
            @Result(property = "memUsedPercent", column = "mem_used_percent"),
            @Result(property = "totalCPUUsage", column = "total_cpu_usage"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    @Select({
            "SELECT sd.*",
            "FROM server_data sd",
            "JOIN (",
            "    SELECT host_name, MAX(event_time) AS latest_event_time",
            "    FROM server_data",
            "    GROUP BY host_name",
            ") latest",
            "ON sd.host_name = latest.host_name AND sd.event_time = latest.latest_event_time"
    })
    List<ServerData> findLatestRecordsForEachHostName();


    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "serverId", column = "server_id"),
            @Result(property = "msgSerial", column = "msg_serial"),
            @Result(property = "hostName", column = "host_name"),
            @Result(property = "eventTime", column = "event_time"),
            @Result(property = "memTotal", column = "mem_total"),
            @Result(property = "memAvailable", column = "mem_available"),
            @Result(property = "memUsed", column = "mem_used"),
            @Result(property = "memFree", column = "mem_free"),
            @Result(property = "memUsedPercent", column = "mem_used_percent"),
            @Result(property = "totalCPUUsage", column = "total_cpu_usage"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    @Select({
        "WITH DeviceData AS (\n" +
        "    SELECT ID,SERVER_ID,MSG_SERIAL,HOST_NAME,EVENT_TIME," +
        "    MEM_TOTAL,MEM_AVAILABLE,MEM_USED,MEM_FREE,MEM_USED_PERCENT,TOTAL_CPU_USAGE " +
        "    FROM  VDAM2024.SERVER_DATA " +
        "    WHERE  EVENT_TIME BETWEEN #{startTimestamp}  AND #{endTimestamp} ) " +
        "SELECT\n" +
        "    ID,\n" +
        "    SERVER_ID,\n" +
        "    MSG_SERIAL,\n" +
        "    HOST_NAME,\n" +
        "    EVENT_TIME,\n" +
        "    MEM_TOTAL,\n" +
        "    MEM_AVAILABLE,\n" +
        "    MEM_USED,\n" +
        "    MEM_FREE,\n" +
        "    MEM_USED_PERCENT,\n" +
        "    TOTAL_CPU_USAGE\n" +
        "FROM\n" +
        "    DeviceData\n" +
        "WHERE\n" +
        "    MSG_SERIAL % #{step} = 0  \n" +
        "ORDER BY\n" +
        "    HOST_NAME, EVENT_TIME;\n"
    })
    List<ServerData> findRecordsByTimeAndStep(@Param("startTimestamp") Timestamp startTimestamp,
                                              @Param("endTimestamp") Timestamp endTimestamp,
                                              @Param("step") int step);

}
